﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using Microsoft.Data.SqlClient;

namespace WHLRDF.ORM.SqlServer
{
    /// <summary>
    /// MySQL数据库引擎帮助类
    /// </summary>
    public class DataAccess
    {
        private static DataAccess _sqlServerDataAccess;
        private static object _locker = new object();
        public static DataAccess Instance
        {
            get
            {
                if (_sqlServerDataAccess == null)
                {
                    lock (_locker)
                    {

                        if (_sqlServerDataAccess == null)
                        {
                            _sqlServerDataAccess = new DataAccess();
                        }
                    }
                }
                return _sqlServerDataAccess;
            }

        }

        #region public string SqlSafe(string value) 检查参数的安全性
        /// <summary>
        /// 检查参数的安全性
        /// </summary>
        /// <param name="value">参数</param>
        /// <returns>安全的参数</returns>
        public string SqlSafe(string value)
        {
            value = value.Replace("'", "''");
            // value = value.Replace("%", "'%");
            return value;
        }
        #endregion

        #region string PlusSign(params string[] values)
        /// <summary>
        ///  获得Sql字符串相加符号
        /// </summary>
        /// <param name="values">参数值</param>
        /// <returns>字符加</returns>
        public string PlusSign(params string[] values)
        {
            string returnValue = string.Empty;
            for (int i = 0; i < values.Length; i++)
            {
                returnValue += values[i] + " + ";
            }
            if (!String.IsNullOrEmpty(returnValue))
            {
                returnValue = returnValue.Substring(0, returnValue.Length - 3);
            }
            else
            {
                returnValue = " + ";
            }
            return returnValue;
        }
        #endregion


        #region public string GetParameter(string parameter) 获得参数Sql表达式
        /// <summary>
        /// 获得参数Sql表达式
        /// </summary>
        /// <param name="parameter">参数名称</param>
        /// <returns>字符串</returns>
        public string GetParameter(string parameter)
        {
            return "@" + parameter + "";
        }

        /// <summary>
        /// 获得参数like Sql表达式
        /// </summary>
        /// <param name="parameter">参数名称</param>
        /// <returns>字符串</returns>
        public string GetLikeParameter(string parameter)
        {
            return " '%'+@" + parameter + "+'%'";
        }
        #endregion

        #region public DbParameter MakeInParam(string targetFiled, object targetValue)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数</returns>
        public DbParameter MakeInParam(string targetFiled, object targetValue)
        {
            return new SqlParameter("@" + targetFiled, targetValue);
        }
        #endregion

        #region public DbParameter[] MakeParameters(string targetFiled, object targetValue)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数集</returns>
        public DbParameter[] MakeParameters(string targetFiled, object targetValue)
        {
            DbParameter[] dbParameters = null;
            if (targetFiled != null && targetValue != null)
            {
                dbParameters = new DbParameter[1];
                dbParameters[0] = this.MakeInParam(targetFiled, targetValue);
            }
            return dbParameters;
        }
        #endregion

        #region public DbParameter[] MakeParameters(string[] targetFileds, Object[] targetValues)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数集</returns>
        public DbParameter[] MakeParameters(string[] targetFileds, Object[] targetValues)
        {
            // 这里需要用泛型列表，因为有不合法的数组的时候
            List<DbParameter> dbParameters = new List<DbParameter>();
            if (targetFileds != null && targetValues != null)
            {
                for (int i = 0; i < targetFileds.Length; i++)
                {
                    if (targetFileds[i] != null && targetValues[i] != null && (!(targetValues[i] is Array)))
                    {
                        dbParameters.Add(this.MakeInParam(targetFileds[i], targetValues[i]));
                    }
                }
            }
            return dbParameters.ToArray();
        }
        #endregion

        public DbParameter MakeParam(string paramName, DbType dbType, Int32 size, ParameterDirection Direction, object value)
        {
            SqlParameter param = new SqlParameter(paramName, value);
            param.DbType = dbType;

            if (size > 0)
            {
                param.Size = size;
                //param = new SqlParameter(paramName, GetDbType(DbType), Size);
            }
            //else
            //{
            //    param = new SqlParameter(paramName, GetDbType(DbType));
            //}

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && value == null))
                param.Value = value;

            return param;
        }

        #region public void SqlBulkCopyData(DataTable dataTable)
        /// <summary>
        /// 利用Net SqlBulkCopy 批量导入数据库,速度超快
        /// </summary>
        /// <param name="dataTable">源内存数据表</param>
        public void SqlBulkCopyData(DataTable dt,string connectionString)
        {
            using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))//引用SqlBulkCopy  
            {
                try
                {
                    sqlRevdBulkCopy.DestinationTableName = dt.TableName;//数据库中对应的表名  
                    sqlRevdBulkCopy.NotifyAfter = dt.Rows.Count;//有几行数据  
                    foreach (DataColumn column in dt.Columns)
                    {
                        sqlRevdBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                    }
                    sqlRevdBulkCopy.WriteToServer(dt);//数据导入数据库  
                }
                catch (Exception)
                {

                }
                finally
                {
                    sqlRevdBulkCopy.Close();//关闭连接  
                }
            }
        }
        #endregion

        #region
        public DbConnection GetConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }

        public DbDataAdapter GetDataAdapter()
        {
            return new SqlDataAdapter();
        }

        public string Now()
        {
            return "select getdate()";
        }
        public DbParameter[] GetParameter(List<DataParameter> parameters)
        {
            if (parameters != null && parameters.Count > 0)
            {
                DbParameter[] parameter = new DbParameter[parameters.Count];
                var index = 0;
                foreach (var item in parameters)
                {
                    parameter[index] = this.MakeParam(item.ParameterName, item.DbType, item.Size, item.Direction, item.Value);
                    index++;

                }
                return parameter;
            }
            return null;
        }

        public DbParameter GetParameter(string name, object value)
        {
            return this.MakeInParam(name, value);
        }


        public string Identity(string where)
        {
            return " output inserted." + where;
        }

        #endregion

        /// <summary>
        /// 禁用自增
        /// </summary>
        /// <returns></returns>
        public string Identity_Disabled(string tableName)
        {
            return "SET IDENTITY_INSERT [" + tableName + "] ON";
        }
        /// <summary>
        /// 启用自增
        /// </summary>
        /// <returns></returns>
        public string Identity_Enable(string tableName)
        {
            return "SET IDENTITY_INSERT [" + tableName + "] ON";
        }

        public string QueryPage(string version,string hql, Order[] orders,
            int pageIndex, int pageSize)
        {
            StringBuilder strsql = new StringBuilder();

            if (string.IsNullOrWhiteSpace(version)|| version.ToInt()>=2012)
            {
                strsql.Append(" " + hql + " ");
            }
           
    
            string strOrder = "";
            int fristIndex = (pageIndex - 1) * pageSize;
            int maxIndex = pageIndex * pageSize;
            if (orders != null && orders.Length > 0)
            {
                int index = 0;
                foreach (var order in orders)
                {
                    if (order == null)
                    { continue; }
                    if (index >= 1)
                        strOrder += ",";
                    else
                        strOrder = " order by ";
                    index++;
                    strOrder += order.ToString();
                }
            }

            if (!string.IsNullOrWhiteSpace(version) && version.ToInt()<2012)
            {
                if (pageIndex > 0)
                {
                    fristIndex = fristIndex + 1;
                    hql = "select ROW_NUMBER() over(" + strOrder + ") as RowIndex," + hql.Substring(hql.IndexOf("select ") + 7);
                    return " select * from ( " + hql + " {0}) as tb " + (pageIndex > 0 ? " where RowIndex between " + fristIndex + " and  " + maxIndex : " ") + "  ";
                }
                else
                {
                    return hql + " {0} ";
                }
            }
            else
            {
                strsql.Append(strOrder + (pageIndex > 0 ? " offset " + fristIndex + " rows fetch next " + pageSize + " rows only " : " "));
            }

            return strsql.ToString();
        }
      
        public string RecordCount(string hql, bool createSql = true)
        {
            if (!createSql)
            {
                return hql;
            }
            return " select count(1) from (" + hql + ") as temptable ";
        }
        /// <summary>
        /// 获取统计查询总数Sql
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public string GetRecordCountSql(string tableName)
        {
            return " select count(1) from "+ tableName + " where 1=1 {0}";
        }
        /// <summary>
        /// 获取查询表Sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="queryFieldBuilder"></param>
        /// <param name="isDeleted"></param>
        /// <returns></returns>
        public string GetSelectSql(string tableName,string queryFieldBuilder="*",bool isDeleted=true)
        {
            return "select " + queryFieldBuilder + " from " + tableName + " where 1=1 " + (isDeleted ? " and " + EntityBase.__IsDeleted + "=0 " : "") + " {0} ";
        }

        #region idbtype

        /// <summary>
        /// 获取数据源中的表或者视图
        /// </summary>
        /// <param name="dbName">服务器名称</param>
        /// <param name="dbType">U表示表 V表示视图</param>
        /// <returns></returns>
        public string GetTableOrView(string dbType="U")
        {
            return @" Select 0 as DbTableId,Name as TableName,Name as  DbTableCation,Name as  DbTableCationEn,case when XType='u' then 1 else 0 end as IsTable 
                 FROM [SysObjects] Where XType='"+ dbType + "' ORDER BY Name  ";
        }
        public string GetDbColumn(string tableName,  bool IsTable)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(@"
               
                SELECT  
                NNumber=a.colorder,   
                FieldName=a.name,
                IsIdentity=COLUMNPROPERTY(a.id,a.name,'IsIdentity'),   
                IsPrimary=case   when   exists(SELECT   1   FROM   [sysobjects]   where   xtype='PK'   and   name   in   (SELECT   name   FROM   [sysindexes]   WHERE   indid   in(SELECT   indid   FROM   [sysindexkeys]   WHERE   id   =   a.id   AND   colid=a.colid)))   then   1   else   0   end,   
                FieldType=b.name,   
                ByteLength=a.length,   
                NLength=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   
                NScale=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   
                NIsNull=a.isnullable,   
                DefaultValue=isnull(e.text,''),
                Comments = ex.value ,
                DbTypeName= b.name       
                FROM   [syscolumns]   a     
                left   join   [systypes]   b   on   a.xtype=b.xusertype     
                inner   join   [sysobjects]   d   on   a.id=d.id   and   d.xtype='" + (IsTable ? "U" : "V") + @"'   and   d.name<>'dtproperties'   
                left   join   [syscomments]  e   on   a.cdefault=e.id  
                LEFT OUTER JOIN   sys.extended_properties ex    on   ex.major_id = a.id AND ex.minor_id = a.colid  AND ex.name = 'MS_Description' 
             
                where   d.name='" + tableName + "' Order   by a.id,a.colorder");
            return strsql.ToString();
        }
        public string GetSource(string tableName)
        {
            return "select * from " + tableName + " where 1=1 {0} ";
        }

        public string CreateTable(string tableName, List<DbColumnAttribute> dbColumns)
        {
            if (dbColumns == null || dbColumns.Count <= 0)
            {
                return "";
            }

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("CREATE TABLE " + tableName);
            stringBuilder.Append("(");
            var index = 0;
            foreach (var item in dbColumns)
            {
                index++;
                if (index > 1)
                {
                    stringBuilder.Append(",");
                }
                stringBuilder.Append("\r\n " + item.Name + " "+ GetColumnType(item) + " " + (item.Identifier ? " identity(1,1)" : "") + (item.IsPrimaryKey ? " primary key" : "") + (!item.AllowDBNull ? " not null" : "") + GetColumnDefault(item));
            }
            stringBuilder.Append(")");
            return stringBuilder.ToString();
        }
        /// <summary>
        /// 获取字段类型
        /// </summary>
        /// <param name="dbColumn"></param>
        /// <returns></returns>
        private string GetColumnType(DbColumnAttribute dbColumn)
        {
            switch ((SysPropertyType)dbColumn.SysFieldTypeId)
            {
                case SysPropertyType.String:
              // case SysPropertyType.Ntext:
                    return   "nvarchar(" + ((dbColumn.MaxLength != -1) ? dbColumn.MaxLength.ToString() : "max") + ")";
                case SysPropertyType.Ntext:
                    return "ntext";
                case SysPropertyType.Decimal:
                    return dbColumn.ColumnType + "(" + dbColumn.MaxLength.ToString() + "," + dbColumn.Scale.ToString() + ")";
                default:
                    return dbColumn.ColumnType;
            }
        }
        /// <summary>
        /// 构建字段默认值
        /// </summary>
        /// <param name="dbColumn"></param>
        /// <returns></returns>
        private string GetColumnDefault(DbColumnAttribute dbColumn)
        {
            if (string.IsNullOrWhiteSpace(dbColumn.Default))
            {
                return "";
            }
            string defaultValue = "";
            switch ((SysPropertyType)dbColumn.SysFieldTypeId)
            {
                case SysPropertyType.String:
                case SysPropertyType.Ntext:
                    defaultValue = "'" + dbColumn.Default.Replace("'", "") + "'";
                    if (defaultValue.ToLower() == "newid")
                    {
                        defaultValue = " newid()";
                    }
                    break;
                case SysPropertyType.DateTime:
                    defaultValue = " getdate()";
                    break;
                case SysPropertyType.Guid:
                    defaultValue = " newid()";
                    break;
                default:
                    defaultValue = dbColumn.Default;
                    break;
            }
            if (!string.IsNullOrWhiteSpace(defaultValue))
            {

                defaultValue = " default " + defaultValue;
            }
            return defaultValue;
        }
        public string AddColumn(string tableName, DbColumnAttribute columnItem)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("alter table " + tableName + " add  " + columnItem.Name + " " + GetColumnType(columnItem) + " " + (columnItem.Identifier ? " identity(1,1)" : "") + (columnItem.IsPrimaryKey ? " primary key" : "") + (!columnItem.AllowDBNull ? " not null" : "") + GetColumnDefault(columnItem) + ";");
            return stringBuilder.ToString();
        }

        public string DeleteTable(string table)
        {
            StringBuilder stringBuilder = new StringBuilder();
            return stringBuilder.ToString();
        }

        public string DeleteColumn(string table, string columnName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            return stringBuilder.ToString();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string UpdateDescriptionSql(string tableName, string columnName, string description, bool isAdd)
        {
            string stroeName = "sys.sp_addextendedproperty";
            if (string.IsNullOrWhiteSpace(description) && !isAdd)//值为空 并且为修改时
            {
                stroeName = "sys.sp_dropextendedproperty";
            }
            else if (!isAdd)
            {
                stroeName = "sys.sp_updateextendedproperty";
            }
            StringBuilder sqlBuilder = new StringBuilder("EXEC " + stroeName + " ");
            sqlBuilder.Append("@name=N'MS_Description',");
            if (!string.IsNullOrWhiteSpace(description))
            {
                sqlBuilder.Append("@value=N'" + description + "',");
            }
            sqlBuilder.Append("@level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'" + tableName + "',@level2type = N'COLUMN', @level2name = N'" + columnName + "'");
            return sqlBuilder.ToString();
        }
        /// <summary>
        /// 判断描述是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">字段名</param>
        /// <returns></returns>
        public string ExistDescriptionSql(string tableName, string columnName)
        {
            StringBuilder stringBuilder = new StringBuilder(@"select 1  FROM   [syscolumns]   a     
                    inner   join [sysobjects]   d   on   a.id = d.id   and   d.xtype = 'U'   and   d.name <> 'dtproperties'
                   inner JOIN   sys.extended_properties ex    on   ex.major_id = a.id AND ex.minor_id = a.colid  AND ex.name = 'MS_Description'
                  where   d.name = '" + tableName + "' and a.name='" + columnName + "' Order   by a.id, a.colorder");
            return stringBuilder.ToString();
        }
        #endregion

    }
}